// 换到名为 dbcomparex 的数据库（如果不存在就创建）。
db = db.getSiblingDB('dbcomparex');

// 创建应用数据库用户
db.createUser({
  user: 'dbcomparex_user',
  pwd: 'a5co_12_av5swa',
  roles: [
    {
      role: 'readWrite',
      db: 'dbcomparex'
    }
  ]
});

// 创建db_info_setting集合并设置验证规则
db.createCollection('db_info_setting', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['db_type', 'connection_name', 'host', 'port', 'username', 'password', 'database_name'],
      properties: {
        db_type: {
          bsonType: 'string',
          description: '数据库类型 (mysql 或 sqlserver)'
        },
        connection_name: {
          bsonType: 'string',
          description: '连接名称'
        },
        host: {
          bsonType: 'string',
          description: '数据库主机地址'
        },
        port: {
          bsonType: 'string',
          description: '数据库端口'
        },
        username: {
          bsonType: 'string',
          description: '数据库用户名'
        },
        password: {
          bsonType: 'string',
          description: '数据库密码'
        },
        database_name: {
          bsonType: 'string',
          description: '数据库名称'
        },
        connection_string_ext: {
          bsonType: ['string', 'null'],
          description: '额外的连接字符串参数'
        },
        created_at: {
          bsonType: 'date',
          description: '创建时间'
        },
        created_by: {
          bsonType: 'string',
          description: '创建人'
        },
        updated_at: {
          bsonType: ['date', 'null'],
          description: '更新时间'
        },
        updated_by: {
          bsonType: ['string', 'null'],
          description: '更新人'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引提高查询性能
db.db_info_setting.createIndex({ "db_type": 1 });
db.db_info_setting.createIndex({ "connection_name": 1 }, { unique: true });
db.db_info_setting.createIndex({ "host": 1 });

// 插入示例数据
db.db_info_setting.insertMany([
  {
    db_type: 'mysql',
    connection_name: 'MySQL示例',
    host: 'mysql.example.com',
    port: '3306',
    username: 'root',
    password: 'mysql_password',
    database_name: 'example_db',
    connection_string_ext: 'charset=utf8mb4',
    created_at: new Date(),
    created_by: 'system',
    updated_at: null,
    updated_by: null
  },
  {
    db_type: 'sqlserver',
    connection_name: 'SQL Server示例',
    host: 'sqlserver.example.com',
    port: '1433',
    username: 'sa',
    password: 'sqlserver_password',
    database_name: 'example_db',
    connection_string_ext: 'Encrypt=yes;TrustServerCertificate=yes',
    created_at: new Date(),
    created_by: 'system',
    updated_at: null,
    updated_by: null
  }
]);

// 1. 创建 task_data_sync_setting 数据同步任务配置表
db.createCollection('task_data_sync_setting', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['task_name', 'db_info_setting_id', 'table_name', 'query_condition', 'cron_expression', 'status'],
      properties: {
        task_name: {
          bsonType: 'string',
          description: '任务名称'
        },
        db_info_setting_id: {
          bsonType: 'string',
          description: '数据库源配置ID'
        },
        table_name: {
          bsonType: 'string',
          description: '表名'
        },
        query_condition: {
          bsonType: 'string',
          description: '数据获取条件'
        },
        cron_expression: {
          bsonType: 'string',
          description: 'Cron表达式'
        },
        status: {
          bsonType: 'int',
          description: '启用状态 (0 禁用，1启用)'
        },
        data_limit: {
          bsonType: ['int', 'null'],
          description: '获取数据量限制'
        },
        created_at: {
          bsonType: 'date',
          description: '创建时间'
        },
        created_by: {
          bsonType: 'string',
          description: '创建人'
        },
        updated_at: {
          bsonType: ['date', 'null'],
          description: '更新时间'
        },
        updated_by: {
          bsonType: ['string', 'null'],
          description: '更新人'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引
db.task_data_sync_setting.createIndex({ "db_info_setting_id": 1 });
db.task_data_sync_setting.createIndex({ "status": 1 });
db.task_data_sync_setting.createIndex({ "task_name": 1 });

// 2. 创建 task_data_sync_setting_detail 数据同步任务配置子表
db.createCollection('task_data_sync_setting_detail', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['task_data_sync_setting_id', 'column_name'],
      properties: {
        task_data_sync_setting_id: {
          bsonType: 'string',
          description: '数据同步任务配置ID'
        },
        column_name: {
          bsonType: 'string',
          description: '列名'
        },
        comment: {
          bsonType: ['string', 'null'],
          description: '备注'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引
db.task_data_sync_setting_detail.createIndex({ "task_data_sync_setting_id": 1 });
db.task_data_sync_setting_detail.createIndex({ 
  "task_data_sync_setting_id": 1, 
  "column_name": 1 
}, { unique: true });

// 3. 创建 task_data_sync_ts_mapping 数据同步目源关系表
db.createCollection('task_data_sync_ts_mapping', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['task_data_sync_setting_id', 'target_column_name', 'db_info_setting_id', 'source_table_name', 'source_column_name', 'priority'],
      properties: {
        task_data_sync_setting_id: {
          bsonType: 'string',
          description: '数据同步任务配置ID'
        },
        target_column_name: {
          bsonType: 'string',
          description: '目标数据列名'
        },
        db_info_setting_id: {
          bsonType: 'string',
          description: '数据库源配置ID'
        },
        source_table_name: {
          bsonType: 'string',
          description: '源表名'
        },
        source_column_name: {
          bsonType: 'string',
          description: '源表列名'
        },
        condition: {
          bsonType: ['string', 'null'],
          description: '条件'
        },
        priority: {
          bsonType: 'int',
          description: '优先级'
        },
        target_query_column: {
          bsonType: ['string', 'null'],
          description: '目标表查询列名'
        },
        source_query_column: {
          bsonType: ['string', 'null'],
          description: '源表查询列名'
        },
        created_at: {
          bsonType: 'date',
          description: '创建时间'
        },
        created_by: {
          bsonType: 'string',
          description: '创建人'
        },
        updated_at: {
          bsonType: ['date', 'null'],
          description: '更新时间'
        },
        updated_by: {
          bsonType: ['string', 'null'],
          description: '更新人'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引
db.task_data_sync_ts_mapping.createIndex({ "task_data_sync_setting_id": 1 });
db.task_data_sync_ts_mapping.createIndex({ 
  "task_data_sync_setting_id": 1, 
  "target_column_name": 1,
  "priority": 1 
});
db.task_data_sync_ts_mapping.createIndex({ "db_info_setting_id": 1 });

// 4. 创建 task_data_sync_execution_log 数据同步任务执行日志表
db.createCollection('task_data_sync_execution_log', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['task_data_sync_setting_id', 'batch_no', 'status'],
      properties: {
        task_data_sync_setting_id: {
          bsonType: 'string',
          description: '数据同步任务配置ID'
        },
        batch_no: {
          bsonType: 'string',
          description: '执行批次号 (TDYYYYMMDD_序号)'
        },
        status: {
          bsonType: 'int',
          description: '状态 (-1 失败，1 执行中，2 数据对比正常，3 数据对比失败)'
        },
        message: {
          bsonType: ['string', 'null'],
          description: '信息 (可以记录异常信息)'
        },
        created_at: {
          bsonType: 'date',
          description: '创建时间'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引
db.task_data_sync_execution_log.createIndex({ "task_data_sync_setting_id": 1 });
db.task_data_sync_execution_log.createIndex({ "batch_no": 1 }, { unique: true });
db.task_data_sync_execution_log.createIndex({ "status": 1 });

// 5. 创建 task_target_list 同步目标数据表
db.createCollection('task_target_list');
// 这个集合没有固定的字段，所以不做验证规则

// 创建索引
db.task_target_list.createIndex({ "task_data_sync_execution_log_id": 1 });

// 6. 创建 task_source_list 同步来源数据表
db.createCollection('task_source_list');
// 这个集合没有固定的字段，所以不做验证规则

// 创建索引
db.task_source_list.createIndex({ "task_data_sync_execution_log_id": 1 });
db.task_source_list.createIndex({ "task_target_list_id": 1 });

// 7. 创建 task_ts_contrast_result 目源对比结果表
db.createCollection('task_ts_contrast_result', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['task_data_sync_execution_log_id', 'task_target_list_id', 'task_source_list_id', 'column_name'],
      properties: {
        task_data_sync_execution_log_id: {
          bsonType: 'string',
          description: '数据同步任务执行日志ID'
        },
        task_target_list_id: {
          bsonType: 'string',
          description: '同步目标数据ID'
        },
        task_source_list_id: {
          bsonType: 'string',
          description: '同步来源数据ID'
        },
        column_name: {
          bsonType: 'string',
          description: '列名'
        },
        target_data: {
          description: '目标数据'
        },
        source_data: {
          description: '源数据'
        },
        is_same: {
          bsonType: 'bool',
          description: '是否相同'
        }
      }
    }
  },
  validationLevel: 'moderate'
});

// 创建索引
db.task_ts_contrast_result.createIndex({ "task_data_sync_execution_log_id": 1 });
db.task_ts_contrast_result.createIndex({ "task_target_list_id": 1 });
db.task_ts_contrast_result.createIndex({ "task_source_list_id": 1 });

// 创建用户集合
db.createCollection("users", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["username", "password", "role", "status", "created_at"],
      properties: {
        username: {
          bsonType: "string",
          description: "用户名"
        },
        password: {
          bsonType: "string",
          description: "密码(加密存储)"
        },
        role: {
          bsonType: "string",
          description: "角色(admin, user)"
        },
        email: {
          bsonType: ["string", "null"],
          description: "邮箱"
        },
        real_name: {
          bsonType: ["string", "null"],
          description: "真实姓名"
        },
        avatar: {
          bsonType: ["string", "null"],
          description: "头像URL"
        },
        status: {
          bsonType: "int",
          description: "状态(0禁用,1启用)"
        },
        last_login_time: {
          bsonType: ["date", "null"],
          description: "上次登录时间"
        },
        last_login_ip: {
          bsonType: ["string", "null"],
          description: "上次登录IP"
        },
        created_at: {
          bsonType: "date",
          description: "创建时间"
        },
        created_by: {
          bsonType: ["string", "null"],
          description: "创建人"
        },
        updated_at: {
          bsonType: ["date", "null"],
          description: "更新时间"
        },
        updated_by: {
          bsonType: ["string", "null"],
          description: "更新人"
        }
      }
    }
  },
  validationLevel: "moderate"
});

// 创建索引
db.users.createIndex({ "username": 1 }, { unique: true });
db.users.createIndex({ "email": 1 });
db.users.createIndex({ "status": 1 });

// 创建初始管理员用户
db.users.insertOne({
  username: "admin",
  // 默认密码: admin123 (实际应用中应该是加密后的)
  password: "$2b$12$kGwfHVA2msKBEQX1S.HEtu4xhjnrFRXMQEQLInMSYuuLO/mqlmQZu",
  role: "admin",
  email: "admin@example.com",
  real_name: "系统管理员",
  status: 1,
  created_at: new Date(),
  created_by: "system"
});